package cn.cuilan.other.xlsx;

import cn.hutool.core.annotation.Alias;
import cn.hutool.core.io.resource.ResourceUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import lombok.Data;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.List;

/**
 * @author zhangyan
 * @since 2021/12/18
 */
public class ImportXlsx {

    private static final String excelFile = "/Users/zhangyan/Desktop/2022-day-1.csv";

    public static void main(String[] args) throws SQLException {
        long time = System.currentTimeMillis();
        ExcelReader reader = ExcelUtil.getReader(ResourceUtil.getStream(excelFile));
        List<MicroStation> stationList = reader.readAll(MicroStation.class);
        for (MicroStation station : stationList) {
            String lnglat = station.getLnglat();
            String[] split = lnglat.split("，");
            BigDecimal lng = new BigDecimal(split[0].trim());
            BigDecimal lat = new BigDecimal(split[1].trim());
            Entity entity = Entity.create("t_micro_station")
                    .set("`no`", StrUtil.isBlank(station.getNo()) ? "" : station.getNo())
                    .set("ob_time", StrUtil.isBlank(station.getObTime()) ? "" : station.getObTime())
                    .set("station_name", StrUtil.isBlank(station.getStationName()) ? "" : station.getStationName())
                    .set("lng", lng)
                    .set("lat", lat)
                    .set("aqi_level", station.getAqiLevel())
                    .set("pollutants", station.getPollutants())
                    .set("pollutants_density", station.getPollutantsDensity() == null ? 0 : station.getPollutantsDensity())
                    .set("so2", station.getSo2() == null ? 0 : station.getSo2())
                    .set("no2", station.getNo2() == null ? 0 : station.getNo2())
                    .set("pm25", station.getPm25() == null ? 0 : station.getPm25())
                    .set("pm10", station.getPm10() == null ? 0 : station.getPm10())
                    .set("co", station.getCo() == null ? new BigDecimal("0.0") : station.getCo())
                    .set("o3", station.getO3() == null ? 0 : station.getO3())
                    .set("hcho", station.getHcho() == null ? 0 : station.getHcho())
                    .set("source", station.getSource())
                    .set("`action`", 0)
                    .set("create_time", time)
                    .set("action_time", time);
//            Db.use().insert(entity);
        }
    }

    @Data
    public class MicroStation {
        @Alias("编号")
        private String no;
        private String obTime;
        private String stationName;

        // 表中没有，只为excel读取
        private String lnglat;

        private BigDecimal lng;
        private BigDecimal lat;
        private String aqiLevel;
        private String pollutants;
        private BigDecimal pollutantsDensity;
        private Integer so2;
        private Integer no2;
        private Integer pm25;
        private Integer pm10;
        private BigDecimal co;
        private Integer o3;
        private Integer hcho;
        private String source;
        private Integer action;
        private Long actionTime;
        private Long createTime;
    }

}
